************************************************************************
* Earmarked Funding Dataset
************************************************************************

use "Component 2", clear
  

*************
* Component 3
*************

preserve
  
  g emidx=thm+2*prj+reg+2*cty+inst+staffco+2*staffbi 
  g mbi=emidx>0 if emidx!=.
  keep if DAC==1
  
  replace mbi=1 if mbi==.
  
  collapse (sum)usd_commitment_defl usd_disbursement_defl, by(iso3 year mbi)
  rename usd_com c
  rename usd_disb d
  reshape wide c d, i(iso3 year) j(mbi)
  order iso3 year c1 d1 c0 d0  
   
  list iso3 year c1 d1 if iso3=="GAVI"|iso3=="GEF"|iso3=="GF"|iso3=="NDF"|iso3=="AF"|iso3=="GCF"|iso3=="CIFs"
  
  sort iso3 year 
  merge iso3 year using "C:\berni\forschung\FLF projects\Data\Global funds replenishments"
  drop if _me==2
  drop _me
  
  egen gavi_out=rowmax(c1 d1) if iso3=="GAVI"
  egen GAVI_OUT=mean(gavi_out), by(year)
  
  egen gef_out=rowmax(c1 d1) if iso3=="GEF"
  egen GEF_OUT=mean(gef_out), by(year) 
  
  egen gf_out=rowmax(c1 d1) if iso3=="GF"
  egen GF_OUT=mean(gf_out), by(year)
  
  egen af_out=rowmax(c1 d1) if iso3=="AF"
  egen AF_OUT=mean(af_out), by(year)
  
  egen gcf_out=rowmax(c1 d1) if iso3=="GCF"
  egen GCF_OUT=mean(gcf_out), by(year)
  
  egen ndf_out=rowmax(c1 d1) if iso3=="NDF"
  egen NDF_OUT=mean(ndf_out), by(year)
  
  egen cifs_out=rowmax(c1 d1) if iso3=="CIFs"
  egen CIFS_OUT=mean(cifs_out), by(year)
  
  g ndf=.
  replace ndf=28.14 if iso3=="DNK"
  replace ndf=20.49 if iso3=="FIN"
  replace ndf=1.43 if iso3=="ISL"
  replace ndf=17.86 if iso3=="NOR"
  replace ndf=32.08 if iso3=="SWE"
  
  g cifs=.
  replace cifs=0.31 if iso3=="AUS"
  replace cifs=5.66 if iso3=="CAN"
  replace cifs=0.26 if iso3=="DNK"
  replace cifs=0.90 if iso3=="DEU"
  replace cifs=12.53 if iso3=="JPN"
  replace cifs=2.10 if iso3=="NOR"
  replace cifs=1.24 if iso3=="ESP"
  replace cifs=0.73 if iso3=="SWE"
  replace cifs=0.16 if iso3=="CHE"
  replace cifs=43.78 if iso3=="GBR"
  replace cifs=32.33 if iso3=="USA"
  
  foreach z in gavi gf gef af gcf ndf cifs  GAVI_OUT GF_OUT GEF_OUT NDF_OUT CIFS_OUT AF_OUT GCF_OUT{
   replace `z'=0 if `z'==.
   }
  
  g rep=(gavi*GAVI_OUT+gef*GEF_OUT+gf*GF_OUT +af*AF_OUT+gcf*GCF_OUT +ndf*NDF_OUT+cifs*CIFS_OUT)/100
  replace rep=0 if rep==.
  lab var rep "Pro-rata multi-bi outflows repatriated to donor"
  
  drop if iso3=="GAVI"|iso3=="GEF"|iso3=="GF"|iso3=="NDF"|iso3=="AF"|iso3=="GCF"|iso3=="CIFs"
  drop gavi gf gef af gcf ndf cifs *_out *_OUT
  order iso3 year c1 d1 rep 
  
  rename c1 mbi_bi
  lab var mbi "Multi-bi aid commitments (via direct delegation to IDOs)"
  rename d1 mbi_bid
  lab var mbi_bid "Multi-bi aid disbursements (via direct delegation to IDOs)"
  rename rep mbi_rep 
  rename c0 othml
  lab var othml "Other multilateral commitments (not reported in DAC1a)"
  rename d0 othmld
  lab var othmld "Other multilateral disbursements (not reported in DAC1a)"
  
  * Courtesy merge of remaining donor flows
  sort iso3 year
  merge iso3 year using "DAC1a_donors_1990-2021_COM"
  tab _me
  drop if _me==2
  drop _me
  
  rename ml ml0
  egen ml=rowtotal(ml0 othml)
  replace ml=ml-max(mbi_rep, 0)
  replace bi=bi-max(mbi_bi, 0)
  replace ml=0 if iso3=="EU"
  
  lab var bi "Bilateral aid (adjusted by mbi_bi)"
  lab var ml "Multilateral aid (adjusted by mbi_rep)"
  
  order iso year mbi* bi ml 
  keep iso year mbi* bi ml 

  sort iso3 year 
  saveold "Component 3", replace v(11)
  
restore   

  
*************
* Component 4
*************

preserve

  * Data preparation 
  
  ** Duplicate codes  
  replace parentID=41144 if parentID==41302
  replace childID=41144 if childID==41302
  
  replace parentID=41143 if parentID==41307
  replace childID=41143 if childID==41307
  
  replace parentID=41107 if parentID==41312
  replace childID=41107 if childID==41312
  
  replace parentID=41141 if parentID==41311
  replace childID=41141 if childID==41311
  
  replace parentID=41000 if parentID==41305 
  replace childID=41000 if childID==41305
  
  replace parentID=42000 if parentID>42000 & parentID<42004
  
  replace parentID=44000 if parentID>=44001 & parentID<=44003
  replace childID=44000 if childID>=44001 & childID<=44003
  
  replace parentID=46002 if parentID==46003
  
  replace parentID=46004 if parentID==46005
  
  
  * Earmarking index 
  g emidx=thm+2*prj+reg+2*cty+ inst+staffco+2*staffbi 
  
  * Aggregation into IO-year format 
  collapse (count)i (sum)usd_commitment_defl usd_disbursement_defl, by(parentID year emidx)
  rename usd_c mbi
  rename usd_d mbid 
  drop if emidx==.
  drop if parentID==. 
  reshape wide i* mbi* , i(parentID year) j(emidx)
  
  egen n=rowtotal(i1 i2 i3 i4 i5 i6 i7)
  lab var n "Number of earmarked projects"
  egen mbi_com=rowtotal(mbi1 mbi2 mbi3 mbi4 mbi5 mbi6 mbi7)
  lab var mbi_com "Earmarked funding commitments received"
  egen mbi_dis=rowtotal(mbid1 mbid2 mbid3 mbid4 mbid5 mbid6 mbid7) 
  lab var mbi_dis "Earmarked funding disbursements received"
  
  * Core funding information 
  sort parentID year 
  merge parentID year using "CRS_MUSE_2011-2021_DAC"
  tab _merge
  drop if _merge==2
  drop _merge acronym 
  lab var core "Unearmarked (core) funding commitments (CRS MUSE table)"
   
  * Labeling 
  forvalues v=1/7{
    lab var i`v' "Number of projects with earmarking index idx=`v'"
	lab var mbi`v' "Cumulative commitments from projects with earmarking index idx=`v'"
	lab var mbid`v' "Cumulative disbursements from projects with earmarking index idx=`v'"
  }
  lab var i0 "Number of non-earmarked projects"
  lab var mbi0 "Cumulative commitments from non-earmarked projects"
  lab var mbid0 "Cumulative disbursements from non-earmarked projects"
  
  sort parentID 
  merge parentID using "ParentID labels"
  tab _merge 
  drop if _me==2
  drop _me
  
  drop if parentID==30000|parentID==40000|parentID==31000|parentID==47000
  
  order parentID acr year n mbi_com mbi_dis core i* mbi0 mbi1 mbi2 mbi3 mbi4 mbi5 mbi6 mbi7 mbid*

  sort parentID year 
  saveold "Component 4", replace v(11)
  
restore 
